Problem
During an upgrade from a previous release of iMIS, you receive an error message stating that “a query uses non-ANSI join operators” and the upgrade fails.
Solution
This problem occurs only in Microsoft SQL Server 2005 environments and is most likely if you are running your current iMIS database in SQL 2000 compatibility mode.
The upgrade scripts for iMIS in a SQL Server 2005 environment require all objects in the iMIS database to be using the ANSI-standard join clauses introduced many years ago in Microsoft SQL Server 6.5. This error is generally caused by a custom trigger or stored procedure using the old-style join syntax that overloads the WHERE clause to provide both joins and restrictions. Specifically, the problem is caused by the *= and =* outer join operators, but it’s good practice to ensure that all such joins are declared using an ANSI-standard join clause.
Use the following steps to correct this problem:
1. Restore your original backup of the iMIS database.
2. Review the restored database for custom triggers and stored procedures, and ensure that all such triggers use only ANSI-standard join clauses (INNER JOIN, LEFT OUTER JOIN, and so on).
3. Run the upgrade procedure again.